﻿using System.Collections.Generic;
using System.Text;
using SuperiorModel;
using SuperiorSqlTools;
using System.Data;
using System.Data.SqlClient;

namespace SuperiorShopDataAccess
{
    public class MarketingDataAccess
    {
        public static DataSet GetSetModel(int shopAdminId)
        {
            var sqlManager = new SqlManager();
            SqlParameter[] parms =
            {
                new SqlParameter("@shopAdminId",shopAdminId)
            };
            var sql = "select * from C_FullAmountSet where ShopAdminId=@shopAdminId;";
            sql += " DECLARE @pidlist varchar(500) ";
            sql += " declare @areaList varchar(500) ";
            sql += " select @pidlist= NotHasProductIds,@areaList= NotHasProvinceIds from C_FullAmountSet where ShopAdminId=@shopAdminId ";
            sql += " EXEC('select ImagePath from C_ProductImg where ProductId in('+@pidlist+') and IsDefault=1 and IsDel=0') ";
            sql += " EXEC('select Area_Name from C_Areas where Area_Code in('+@areaList+')') ";
            return sqlManager.ExecuteDataset(CommandType.Text, sql, parms);
        }

        public static void SetNotPids(string ids, int shopAdminId)
        {
            var sqlManager = new SqlManager();
            SqlParameter[] parms =
            {
                new SqlParameter("@shopAdminId",shopAdminId),
                new SqlParameter("@ids",ids)
            };
            var sb = new StringBuilder();
            sb.Append("if exists(select Id from C_FullAmountSet where ShopAdminId=@shopAdminId)");
            sb.Append(" begin ");
            sb.Append(" update C_FullAmountSet set NotHasProductIds=@ids where ShopAdminId=@shopAdminId ");
            sb.Append(" end ");
            sb.Append(" else ");
            sb.Append(" begin ");
            sb.Append(" insert into C_FullAmountSet(ShopAdminId,NotHasProductIds)values(@shopAdminId,@ids) ");
            sb.Append(" end ");
            sqlManager.ExecuteNonQuery(CommandType.Text, sb.ToString(), parms);
        }
        public static void SetNotAreas(string ids, int shopAdminId)
        {
            var sqlManager = new SqlManager();
            SqlParameter[] parms =
            {
                new SqlParameter("@shopAdminId",shopAdminId),
                new SqlParameter("@ids",ids)
            };
            var sb = new StringBuilder();
            sb.Append("if exists(select Id from C_FullAmountSet where ShopAdminId=@shopAdminId)");
            sb.Append(" begin ");
            sb.Append(" update C_FullAmountSet set NotHasProvinceIds=@ids where ShopAdminId=@shopAdminId ");
            sb.Append(" end ");
            sb.Append(" else ");
            sb.Append(" begin ");
            sb.Append(" insert into C_FullAmountSet(ShopAdminId,NotHasProvinceIds)values(@shopAdminId,@ids) ");
            sb.Append(" end ");
            sqlManager.ExecuteNonQuery(CommandType.Text, sb.ToString(), parms);
        }
        public static void SetFullAmount(int optionStatus, int amount, int shopAdminId)
        {
            var sqlManager = new SqlManager();
            SqlParameter[] parms =
            {
                new SqlParameter("@optionStatus",optionStatus),
                new SqlParameter("@amount",amount),
                  new SqlParameter("@shopAdminId",shopAdminId)
            };
            var sb = new StringBuilder();
            sb.Append("if exists(select Id from C_FullAmountSet where ShopAdminId=@shopAdminId)");
            sb.Append(" begin ");
            sb.Append(" update C_FullAmountSet set OptionStatus=@optionStatus,Amount=@amount where ShopAdminId=@shopAdminId ");
            sb.Append(" end ");
            sb.Append(" else ");
            sb.Append(" begin ");
            sb.Append(" insert into C_FullAmountSet(ShopAdminId,OptionStatus,Amount)values(@shopAdminId,@optionStatus,@amount) ");
            sb.Append(" end ");
            sqlManager.ExecuteNonQuery(CommandType.Text, sb.ToString(), parms);
        }

        public static DataSet GetCouponList(int shopAdminId)
        {
            var sqlManager = new SqlManager();
            SqlParameter[] parms =
            {
                new SqlParameter("@shopAdminId",shopAdminId)
            };
            var sql = "select *,isnull((select COUNT(*) from C_UserCoupon where CouponId=c.Id),0)as GetCount from C_Coupon c where ShopAdminId=@shopAdminId and IsDel=0";
            return sqlManager.ExecuteDataset(CommandType.Text, sql, parms);
        }

        public static DataSet SearchAdminCouponDs(AdminCouponCriteria criteria)
        {
            var sqlManager = new SqlManager();
            List<SqlParameter> parms = new List<SqlParameter>();
            var sb = sqlManager.CreateSb();
            if (!string.IsNullOrEmpty(criteria.LoginName))
            {
                sb.Append(" and s.LoginName=@LoginName");
                parms.Add(new SqlParameter("@LoginName", criteria.LoginName));
            }
            if (criteria.CouponType != 999)
            {
                sb.Append(" and c.CouponType=@CouponType ");
                parms.Add(new SqlParameter("@CouponType", criteria.CouponType));
            }
            var sql = string.Format("select top({0}) * from(select ROW_NUMBER() over(order by c.Id desc)as rownum,c.*,isnull((select COUNT(*) from C_UserCoupon where CouponId=c.Id),0)as GetCount,s.LoginName from C_Coupon c left join C_ShopAdmin s on c.ShopAdminId=s.Id where {1} and c.IsDel=0)tt where tt.rownum>{2};select count(*) as totalCount from C_Coupon c left join C_ShopAdmin s on c.ShopAdminId=s.Id where {1} and c.IsDel=0", criteria.PagingResult.PageSize, sb.ToString(), criteria.PagingResult.PageSize * criteria.PagingResult.PageIndex);
            return sqlManager.ExecuteDataset(CommandType.Text, sql, parms.ToArray());
        }

        public static void AddCoupon(CouponModel model)
        {
            var sqlManager = new SqlManager();
            SqlParameter[] parms =
            {
                new SqlParameter("@ShopAdminId",model.ShopAdminId),
                new SqlParameter("@CouponName",model.CouponName),
                 new SqlParameter("@CouponType",model.CouponType),
                  new SqlParameter("@DelAmount",model.DelAmount),
                   new SqlParameter("@Discount",model.Discount),
                    new SqlParameter("@MinOrderAmount",model.MinOrderAmount),
                     new SqlParameter("@Days",model.Days),
                      new SqlParameter("@TotalCount",model.TotalCount),
            };
            var sql = "insert into C_Coupon(ShopAdminId,CouponName,CouponType,DelAmount,Discount,MinOrderAmount,Days,TotalCount)values(@ShopAdminId,@CouponName,@CouponType,@DelAmount,@Discount,@MinOrderAmount,@Days,@TotalCount)";
            sqlManager.ExecuteNonQuery(CommandType.Text, sql, parms);
        }

        public static void DelCoupon(int id)
        {
            var sqlManager = new SqlManager();
            SqlParameter[] parms =
            {
                new SqlParameter("@id",id)
            };
            var sql = "update C_Coupon set IsDel=1 where Id=@id";
            sqlManager.ExecuteNonQuery(CommandType.Text, sql, parms);
        }

        public static DataSet SearchUserCouponList(UserCouponCriteria criteria)
        {
            var sqlManager = new SqlManager();
            List<SqlParameter> parms = new List<SqlParameter>();
            var sb = sqlManager.CreateSb();
            sb.Append(" and cc.ShopAdminId=@ShopAdminId ");
            parms.Add(new SqlParameter("@ShopAdminId", criteria.ShopAdminId));
            if (!string.IsNullOrEmpty(criteria.NickName))
            {
                sb.Append(" and cu.NickName=@nickname ");
                parms.Add(new SqlParameter("@nickname", criteria.NickName));
            }
            if (criteria.CouponType != 999)
            {
                sb.Append(" and cc.CouponType=@coupontype ");
                parms.Add(new SqlParameter("@coupontype", criteria.CouponType));
            }
          
            var sql = string.Format("select top({0}) * from(select ROW_NUMBER() over (order by cuc.Id desc) as rownum ,cu.NickName,cuc.CouponId,cc.CouponName, cc.CouponType,cuc.CreateTime,cu.AppType  from C_UserCoupon cuc left join C_Users cu on cuc.UserId=cu.Id left join C_Coupon cc on cuc.CouponId=cc.Id where {1})tt where tt.rownum>{2}; select count(*) as totalCount from C_UserCoupon cuc left join C_Users cu on cuc.UserId=cu.Id left join C_Coupon cc on cuc.CouponId=cc.Id where {1} ", criteria.PagingResult.PageSize, sb.ToString(), criteria.PagingResult.PageSize * criteria.PagingResult.PageIndex);
            return sqlManager.ExecuteDataset(CommandType.Text, sql, parms.ToArray());
        }

        #region 接口
        public static DataSet Api_GetUserCouponList(int userid)
        {
            var sqlManager = new SqlManager();
            SqlParameter[] parms =
           {
                new SqlParameter("@userid",userid)
            };
            var sql = "select CouponId,c.CouponName,c.CouponType, c.DelAmount,c.Discount,c.MinOrderAmount,uc.CreateTime,c.Days from C_UserCoupon uc left join C_Coupon c on uc.CouponId = c.Id where uc.IsUsed = 0 and uc.UserId = @userid ";
            return sqlManager.ExecuteDataset(CommandType.Text,sql,parms);
        }
        public static DataSet Api_GetUserCoupon(int userid,int couponid)
        {
            var sqlManager = new SqlManager();
            SqlParameter[] parms =
           {
                new SqlParameter("@userid",userid),
                new SqlParameter("@couponid",couponid)
            };
            var sql = "select CouponId,c.CouponName,c.CouponType, c.DelAmount,c.Discount,c.MinOrderAmount,uc.CreateTime,c.Days from C_UserCoupon uc left join C_Coupon c on uc.CouponId = c.Id where uc.IsUsed = 0 and uc.UserId = @userid and uc.CouponId=@couponid";
            return sqlManager.ExecuteDataset(CommandType.Text, sql, parms);
        }

        public static DataSet Api_GetCouponList(int shopAdminId)
        {
            var sqlManager = new SqlManager();
            SqlParameter[] parms =
           {
                new SqlParameter("@shopAdminId",shopAdminId)
            };
            var sql = "select * from C_Coupon where ShopAdminId=@shopAdminId and IsDel=0";
            return sqlManager.ExecuteDataset(CommandType.Text, sql, parms);
        }

        public static int Api_GetCoupon(int userid, int couponid)
        {
            var sqlManager = new SqlManager();
            SqlParameter rtn_err = sqlManager.GetRtnParameter();
            SqlParameter[] parms =
            {
                new SqlParameter("@couponId", couponid),
                new SqlParameter("@userId",userid),
                rtn_err
            };
            sqlManager.ExecuteNonQuery(CommandType.StoredProcedure, "getcoupon", parms);
            if (rtn_err.Value != null)
            {
                return int.Parse(rtn_err.Value.ToString());
            }
            return -1;
        }
        #endregion
    }
}
